#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_dws.dws_attendance

with morning_tmp as(
select
    class_id,
    student_id,
    min(signin_time) as signin_time,
    signin_date,
    if(cast(min(signin_time) as timestamp ) <= (cast(concat(signin_date,' ',min(morning_begin_time)) as timestamp) + interval '10' minute),1,0) as attendance_type,
    if(cast(min(signin_time) as timestamp ) > (cast(concat(signin_date,' ',min(morning_begin_time)) as timestamp) + interval '10' minute),1,0) as later_type
from hive.zx_dwb.dwb_signin_detail
where cast(signin_time as timestamp ) between (cast(concat(signin_date,' ',morning_begin_time) as timestamp) - interval '40' minute )
    and cast(concat(signin_date,' ',morning_end_time) as timestamp )
	and signin_date = '${dt}'
group by class_id, student_id, signin_date
order by signin_time),

morning_attendance_later as(
select
    signin_date,
    class_id,
    sum(attendance_type) as morning_attendance_num,
    sum(later_type) as morning_later_num
from morning_tmp
group by class_id,signin_date),

afternoon_tmp as(
select
    class_id,
    student_id,
    min(signin_time) as signin_time,
    signin_date,
    if(cast(min(signin_time) as timestamp ) <= (cast(concat(signin_date,' ',min(afternoon_begin_time)) as timestamp) + interval '10' minute),1,0) as attendance_type,
    if(cast(min(signin_time) as timestamp ) > (cast(concat(signin_date,' ',min(afternoon_end_time)) as timestamp) + interval '10' minute),1,0) as later_type
from hive.zx_dwb.dwb_signin_detail
where cast(signin_time as timestamp) between (cast(concat(signin_date,' ',afternoon_begin_time) as timestamp) - interval '40' minute )
    and cast(concat(signin_date,' ',afternoon_end_time) as timestamp )
	and signin_date = '${dt}'
group by class_id, student_id, signin_date
order by signin_time),

afternoon_attendance_later as(
select
    signin_date,
    class_id,
    sum(attendance_type) as afternoon_attendance_num,
    sum(later_type) as afternoon_later_num
from afternoon_tmp
group by class_id,signin_date),

evening_tmp as (
select
    class_id,
    student_id,
    min(signin_time) as signin_time,
    signin_date,
    if(cast(min(signin_time) as timestamp ) <= (cast(concat(signin_date,' ',min(evening_begin_time)) as timestamp) + interval '10' minute),1,0) as attendance_type,
    if(cast(min(signin_time) as timestamp ) > (cast(concat(signin_date,' ',min(evening_end_time)) as timestamp) + interval '10' minute),1,0) as later_type
from hive.zx_dwb.dwb_signin_detail
where cast(signin_time as timestamp) between (cast(concat(signin_date,' ',evening_begin_time) as timestamp) - interval '40' minute )
    and cast(concat(signin_date,' ',evening_end_time) as timestamp )
	and signin_date = '${dt}'
group by class_id, student_id, signin_date
order by signin_time),

evening_attendance_later as(
select
    signin_date,
    class_id,
    sum(attendance_type) as evening_attendance_num,
    sum(later_type) as evening_later_num
from evening_tmp
group by class_id,signin_date),

leave_tmp as(
select
    class_date,
    class_id,
    if(cast(min(begin_time) as timestamp) <= cast(concat(class_date,' ',min(morning_end_time)) as timestamp )
        and
       cast(min(end_time) as timestamp) >= cast(concat(class_date,' ',min(morning_begin_time)) as timestamp ),1,0) as morning_leave,

    if(cast(min(begin_time) as timestamp) <= cast(concat(class_date,' ',min(afternoon_end_time)) as timestamp )
        and
       cast(min(end_time) as timestamp) >= cast(concat(class_date,' ',min(afternoon_begin_time)) as timestamp ),1,0) as afternoon_leave,

    if(cast(min(begin_time) as timestamp) <= cast(concat(class_date,' ',min(evening_end_time)) as timestamp )
        and
       cast(min(end_time) as timestamp) >= cast(concat(class_date,' ',min(evening_begin_time)) as timestamp ),1,0) as evening_leave
from hive.zx_dwb.dwb_leave_detail
where audit_state = 1 and valid_state = 1
	and substr(begin_time,1,10) = '${dt}'
group by class_date, class_id),

leave as (
select
    class_date,
    class_id,
    sum(morning_leave) as morning_leave_num,
    sum(afternoon_leave) as afternoon_leave_num,
    sum(evening_leave) as evening_leav_num
from leave_tmp
group by class_date, class_id)

select
    m.signin_date,
    cast(m.class_id as varchar),
    m.morning_attendance_num,
    m.morning_later_num,
    if(l.morning_leave_num is null,0,l.morning_leave_num) as morning_leave_num,
    cssc.studying_student_count - m.morning_attendance_num - m.morning_later_num - if(l.morning_leave_num is null,0,l.morning_leave_num) as morning_truant_num,
    a.afternoon_attendance_num,
    a.afternoon_later_num,
    if(l.afternoon_leave_num is null,0,l.afternoon_leave_num) as afternoon_leave_num,
    cssc.studying_student_count - a.afternoon_attendance_num - a.afternoon_later_num - if(l.afternoon_leave_num is null,0,l.afternoon_leave_num) as afternoon_truant_num,
    e.evening_attendance_num,
    e.evening_later_num,
    if(l.evening_leav_num is null,0,l.evening_leav_num) as evening_leav_num,
    cssc.studying_student_count - e.evening_attendance_num - e.evening_later_num - if(l.evening_leav_num is null,0,l.evening_leav_num) as evening_truant_num,
    cssc.studying_student_count,
    m.signin_date as dt
from morning_attendance_later m
join afternoon_attendance_later a
    on m.class_id = a.class_id and m.signin_date = a.signin_date
join evening_attendance_later e
    on m.class_id = e.class_id and m.signin_date = e.signin_date
left join leave l
    on m.class_id = l.class_id and m.signin_date = l.class_date
left join hive.zx_dwd.dwd_dim_class_studying_student_count cssc
    on m.class_id = cssc.class_id and m.signin_date = cssc.studying_date

"